/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.cabarrus.xltosql;

import com.cabarrus.xltosql.util.Utility;
import java.awt.Frame;
import java.text.Format;
import jxl.Cell;
import jxl.CellType;
import jxl.NumberCell;
import jxl.Sheet;
import jxl.Workbook;
import org.jdesktop.application.Task;
import org.jdesktop.application.TaskService;

/**
 *
 * @author moscac
 */
public class Converter {

    private void validateFile() {
        if (Utility.isTaskRunning("VerifyTask", true, parent)) {
            return;
        }

    }

    private class ConvertToSQLTask extends Task<Void, Void> {

        ConvertToSQLTask() {
            super(org.jdesktop.application.Application.getInstance(XLToSQLApp.class));
            setTitle("ConvertToSQLTask");
        }

        @Override
        protected Void doInBackground() {
            setMessage("Converting to SQL");
            try {
                sheet = workbook.getSheet(0);
                int rows = sheet.getRows();
                Cell cellReceiptNumber = null;
                Cell cellTransactionAmount = null;
                Cell cellEvent = null;
                Cell cellStatus = null;
                String where = "";
                String verifyMessage = "";
                String caseNumber = "";
                double receiptTotal = 0;
                double receiptsTotal = 0;
                double fileTotal = 0;
                for (int row = BEGINNING_ROW; row < rows; row++) {
                    cellReceiptNumber = sheet.getCell(CELL_RECEIPT_NUMBER, row);
                    cellTransactionAmount = sheet.getCell(CELL_TRANSCTION_AMOUNT, row);
                    NumberCell ncTransactionAmount = null;
                    cellEvent = sheet.getCell(CELL_EVENT, row);
                    cellStatus = sheet.getCell(CELL_STATUS, row);
                    if ((cellReceiptNumber == null) || (cellReceiptNumber.getContents().length() == 0)) {
                        break;
                    }
                    if (cellTransactionAmount.getType() != CellType.NUMBER) {
                        verifyMessage = "Unexpected type found in amount cell";

                    } else {
                        //if (cellStatus.getContents().trim().equalsIgnoreCase("Success")) {
                        ncTransactionAmount = (NumberCell) cellTransactionAmount;
                        fileTotal += ncTransactionAmount.getValue();
                        //}
                        verifyMessage = "";
                        where = "where rh_receipt_number = '" + cellReceiptNumber.getContents().trim() + "'";
                        caseNumber = "";
                    }
                    if (verifyMessage.length() > 0) {
                    }
                }
            } finally {
                workbook.close();
            }
            return null;
        }

        @Override
        protected void finished() {

            setMessage("Done.");
        }
    }
    
    private Workbook workbook;
    private Sheet sheet;
    private Frame parent;
    private TaskService taskService;
    private int BEGINNING_ROW = 3;
    private int CELL_RECEIPT_NUMBER = 3;
    private int CELL_TRANSCTION_AMOUNT = 15;
    private int CELL_EVENT = 23;
    private int CELL_STATUS = 26;
    private Format formatter;
}
